
**********CRSP DAILY DATA*********
use PERMNO date RET PRC VOL DLRET DLSTCD CFACPR using "$jfqa_rep/crsp_daily_all.dta", clear

rename PERMNO permno
rename RET ret
rename PRC prc 
rename VOL vol
rename DLRET dlret
rename DLSTCD dlstcd 
rename CFACPR cfacpr

qui{
*delete duplicates and clean prc
drop if date==date[_n-1] & permno==permno[_n-1]
replace prc=abs(prc)

*define dates
gen month=month(date)
gen year=year(date)
gen day=day(date)

*de-listed returns
replace ret=dlret if missing(ret)
replace ret=-0.3 if missing(ret) & dlstcd==500
replace ret=-0.3 if missing(ret) & dlstcd==520
replace ret=-0.3 if missing(ret) & dlstcd>550 & dlstcd<575
replace ret=-0.3 if missing(ret) & dlstcd==580
replace ret=-0.3 if missing(ret) & dlstcd==584
replace ret=-1 if missing(ret) & ~missing(dlstcd)
drop dlstcd dlret

drop if missing(prc) & missing(ret)
}

*define min, max, and volume
qui{
gen monthyear=ym(year, month)
egen min_ret=min(ret), by(permno monthyear)
egen max_ret=max(ret), by(permno monthyear)
gen dollar_vol=prc*vol
egen total_vol=sum(dollar_vol), by(permno monthyear)
drop dollar_vol vol
}

*define dummy for any missing price in the month
qui{
egen dates=count(date), by(permno monthyear)
egen prices=count(prc), by(permno monthyear)
gen miss_dummy=0
replace miss_dummy=1 if dates~=prices
drop dates prices
}

*define adjusted prices for dividends and splits
qui{
gen prc_div=prc if permno~=permno[_n-1] | month~=month[_n-1]
replace prc_div=prc_div[_n-1]*(1+ret) if missing(prc_div)

gen prc_split=prc/cfacpr
drop cfacpr
}

*calculate historical returns
qui{
gen ret_1=ret
gen ret_5=(1+ret)*(1+ret[_n-1])*(1+ret[_n-2])*(1+ret[_n-3])*(1+ret[_n-4])-1
gen ret_10=(1+ret)*(1+ret[_n-1])*(1+ret[_n-2])*(1+ret[_n-3])*(1+ret[_n-4])*(1+ret[_n-5])*(1+ret[_n-6])*(1+ret[_n-7])*(1+ret[_n-8])*(1+ret[_n-9])-1
}

*calculate returns first ten days of month
qui{
gsort permno monthyear date
by permno monthyear: gen obs=_n
gen first_10=(1+ret)*(1+ret[_n-1])*(1+ret[_n-2])*(1+ret[_n-3])*(1+ret[_n-4])*(1+ret[_n-5])*(1+ret[_n-6])*(1+ret[_n-7])*(1+ret[_n-8])*(1+ret[_n-9])-1

replace first_10=. if obs~=10
rename first_10 tmp
egen first_10=max(tmp), by(permno monthyear)
drop tmp
}

*calculate future returns
qui{
sort year month day
merge m:1 year month day using "$jfqa_rep/FF3_Daily.dta"
drop _merge

sort permno year month day

gen futret_1=ret[_n+1]
gen futret_5=(1+ret[_n+1])*(1+ret[_n+2])*(1+ret[_n+3])*(1+ret[_n+4])*(1+ret[_n+5])-1
gen futret_10=(1+ret[_n+1])*(1+ret[_n+2])*(1+ret[_n+3])*(1+ret[_n+4])*(1+ret[_n+5])*(1+ret[_n+6])*(1+ret[_n+7])*(1+ret[_n+8])*(1+ret[_n+9])*(1+ret[_n+10])-1

gen futexcess_1=ret[_n+1]-rf[_n+1]/100
gen futexcess_5=(1+(ret[_n+1]-rf[_n+1]/100))*(1+(ret[_n+2]-rf[_n+2]/100))*(1+(ret[_n+3]-rf[_n+3]/100))*(1+(ret[_n+4]-rf[_n+4]/100))*(1+(ret[_n+5]-rf[_n+5]/100))-1
gen futexcess_10=(1+(ret[_n+1]-rf[_n+1]/100))*(1+(ret[_n+2]-rf[_n+2]/100))*(1+(ret[_n+3]-rf[_n+3]/100))*(1+(ret[_n+4]-rf[_n+4]/100))*(1+(ret[_n+5]-rf[_n+5]/100))*(1+(ret[_n+6]-rf[_n+6]/100))*(1+(ret[_n+7]-rf[_n+7]/100))*(1+(ret[_n+8]-rf[_n+8]/100))*(1+(ret[_n+9]-rf[_n+9]/100))*(1+(ret[_n+10]-rf[_n+10]/100))-1
}

*assign first and last observation of month
qui{
gen prc_div_first=prc_div if month~=month[_n-1] | permno~=permno[_n-1]
egen min_prc=sum(prc_div_first), by(permno monthyear)
replace min_prc=. if min_prc==0
replace prc_div_first=min_prc if missing(prc_div_first)
drop min_prc

gen prc_split_first=prc_split if month~=month[_n-1] | permno~=permno[_n-1]
egen min_prc=sum(prc_split_first), by(permno monthyear)
replace min_prc=. if min_prc==0
replace prc_split_first=min_prc if missing(prc_split_first)
drop min_prc

gen prc_div_last=prc_div if month~=month[_n+1] | permno~=permno[_n+1]
egen min_prc=sum(prc_div_last), by(permno monthyear)
replace min_prc=. if min_prc==0
replace prc_div_last=min_prc if missing(prc_div_last)
drop min_prc

gen prc_split_last=prc_split if month~=month[_n+1] | permno~=permno[_n+1]
egen min_prc=sum(prc_split_last), by(permno monthyear)
replace min_prc=. if min_prc==0
replace prc_split_last=min_prc if missing(prc_split_last)
drop min_prc
}

*calculate within month convexity measures
qui{
egen avgprc=mean(prc_div), by(permno monthyear)
gen convexity_div=((prc_div_first+prc_div_last)/2-avgprc)/((prc_div_first+prc_div_last)/2)
drop avgprc prc_div_first prc_div_last

egen avgprc=mean(prc_split), by(permno monthyear)
gen convexity_split=((prc_split_first+prc_split_last)/2-avgprc)/((prc_split_first+prc_split_last)/2)

drop avgprc prc_split_first prc_split_last
}


keep if permno~=permno[_n+1] | month~=month[_n+1] /*keep month end observation*/

keep permno month year ret_1 ret_5 ret_10 fut* convexity_div convexity_split min_ret max_ret total_vol miss_dummy first_10

sort permno year month
drop if permno==permno[_n-1] & year==year[_n-1] & month==month[_n-1]

save "$jfqa_rep/18_Monthly_Convex.dta", replace
